/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package DBLayer;
import ModelLayer.*;
import java.sql.*;
import java.util.ArrayList;
/**
 *
 * @author Sharif
 */
public class DBSalesOrder implements IFDBSalesOrder {
    private  Connection con;
    public DBSalesOrder()
    {
        con = DBConnection.getInstance().getDBcon();
    }

    public ArrayList getAllSalesOrders(boolean retriveAssociation)
    {
        return miscWhere("", retriveAssociation);
    }
    //get one customer having the id
    public SalesOrder findSalesOrder(int id, boolean retriveAssociation)
    {   String wClause = "  id = " + id;
        return singleWhere(wClause, retriveAssociation);
    }
    //find one customer having the name
    public ArrayList<SalesOrder> findByDeliveryStatus(int deliveryStatus, boolean retriveAssociation)
    {
        String wClause = "deliveryStatus = " + deliveryStatus;
        System.out.println("SearchCostumer " + wClause);
        return miscWhere(wClause, retriveAssociation);
    }

     //insert a new customer
    public int insertSalesOrder(SalesOrder order)
    {  //call to get the next ssn number
        int nextSSN = GetMax.getMaxId("Select max(id) from SalesOrder");
        nextSSN = nextSSN + 1;
        System.out.println("next id = " +  nextSSN);

       int rc = -1;
	   String query="INSERT INTO SalesOrder(id,date,deliveryStatus,deliveryDate,customerId,invoiceId,amount)  VALUES(" +
		   nextSSN + ",'" +
		   order.getDate() + "'," +
		   order.getDeliveryStatus() + ",'" +
		   order.getDeliveryDate() + "'," +
		   order.getCustomer() + "," +
		   order.getInvoice() + "," +
		   order.getAmount() +
		   ")";


      System.out.println("insert : " + query);
      try{ // insert new customer
            Statement stmt = con.createStatement();
	 	  	stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
		}//end try
	 	catch(Exception ex){
	 	  	System.out.println("Insert exception in SalesOrder db: "+ex);
	   }
	   return(rc);
	}

	public int updateSalesOrder(SalesOrder order)
	{
		//SalesOrder
		int rc=-1;

		String query="UPDATE SalesOrder SET "+
		   "id=" + order.getId() + ",'" +
		   "date=" + order.getDate() + "'," +
		   "deliveryStatus=" + order.getDeliveryStatus() + ",'" +
		   "deliveryDate=" + order.getDeliveryDate() + "'," +
		   "customerId=" + order.getCustomer() + "," +
		   "invoiceId=" + order.getInvoice() + "," +
		   "amount=" + order.getAmount() + "," +
		   " WHERE id=" + order.getId();
                System.out.println("Update query:" + query);
  		try{ // update customer
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);

	 	 	stmt.close();
		}
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in SalesOrder db: "+ex);
	  	}
		return(rc);
	}

	public int delete(int id)
	{
               int rc=-1;

	  	String query="DELETE FROM SalesOrder WHERE id =" +
				id;
                System.out.println(query);
	  	try{ // delete from customer
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}
   	        catch(Exception ex){
	 	  	System.out.println("Delete exception in SalesOrder db: "+ex);
   	        }
		return(rc);
	}




    private ArrayList miscWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		ArrayList list = new ArrayList();	
		
		String query =  buildQuery(wClause);
  
  		try{ // read the customer from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		

			while( results.next() ){
		     	 SalesOrder salesOrderObj = new SalesOrder();
			 salesOrderObj = buildSalesOrder(results);
                         list.add(salesOrderObj);	
			}
			stmt.close();
		}	
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}
	
	//Singelwhere is used when we only select one customer
	private SalesOrder singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		SalesOrder salesOrderObj = new SalesOrder();
//              empObj.setId(0);
                String query =  buildQuery(wClause);
                System.out.println(query);
		try{ // read the customer from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() ){
                            salesOrderObj = buildSalesOrder(results);
                            
                            stmt.close();

//                            }
			}
		
		}//end try	
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return salesOrderObj;
	}
	//method to build the query - Mathias: Added join statement
	private String buildQuery(String wClause)
	{
	    String query="Select * from SalesOrder ";                    
            
		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;
			
		return query;
	}
	//method to build an customer object
	private SalesOrder buildSalesOrder(ResultSet results)
        {   
            SalesOrder salesOrderObj = new SalesOrder();
        
        try{
                salesOrderObj.setId(results.getInt(1));
                salesOrderObj.setDate(results.getString(2));
                salesOrderObj.setDeliveryStatus(results.getInt(3));
                salesOrderObj.setDeliveryDate(results.getString(4));
                salesOrderObj.setCustomer(results.getInt(5));
                salesOrderObj.setInvoice(results.getInt(6));
                salesOrderObj.setAmount(results.getDouble(7));
        }
        catch(Exception e)
        {
            System.out.println("error in building the customer object");
        }
            return salesOrderObj;
        }
}
